package com.games.fifteenpieces;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;

public class Saver {
	public static final String DB_NAME = "fifteenpieces";
	public static final int DB_VER = 14;
	
	//we have 2 tables in our database
	public static final String TABLE_GAMES_NAME = "games";
	public static final String TABLE_PIECES_NAME = "pieces";
	
	//column names in GAMES table
	public static final String KEY_GAMES_ID = "_id",
	                           KEY_GAMES_DATE = "date",
	                           KEY_GAMES_PIC = "pic",
	                           KEY_GAMES_COMPLETE = "compl";
	
	//column names in PIECES_COORDS table
	public static final String KEY_PIECES_GAME_ID = "game_id",
                	           KEY_PIECES_KEY = "key",
                	           KEY_PIECES_COORD = "coord";
	
	private static final String SQL_CREATE_TABLE_GAMES = "create table games(_id integer primary key autoincrement, " + 
	                                                 	"date text not null, pic text not null, compl integer not null)";
	private static final String SQL_CREATE_TABLE_PIECES = "create table pieces(_id integer primary key autoincrement, " + 
														"game_id integer not null, key text not null, coord integer not null)";

	private final Context mContext;
	private DBHelper mDbHelper;
	private SQLiteDatabase mDb;
	
	private static class DBHelper extends SQLiteOpenHelper{

		public DBHelper(Context context) {
			super(context, DB_NAME, null, DB_VER);
			// TODO Auto-generated constructor stub
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			// TODO Auto-generated method stub
			//Log.w("-= DBHelper =-", "Create database: " + SQL_CREATE_DATABASE);
			db.execSQL(SQL_CREATE_TABLE_GAMES);
			db.execSQL(SQL_CREATE_TABLE_PIECES);
			//
			//db.execSQL("insert into games(date, pic, compl) values(16, \"16\", 16)");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			// TODO Auto-generated method stub
			Log.w("-= DBHelper =-", "Upgrade database from " + oldVersion + " to " + newVersion);
			db.execSQL("drop table if exists games");
			db.execSQL("drop table if exists pieces");
			onCreate(db);
		}
		
	}
	
	public Saver(Context context){
		mContext = context;
		ContentValues v = new ContentValues();
		Bundle b = new Bundle();
		b.putParcelable("12", v);
	}
	
	public Saver open(){
		mDbHelper = new DBHelper(mContext);
		mDb = mDbHelper.getWritableDatabase();
		return this;
	}
	
	public Cursor fetchAllGames(){
		String[] columns = new String[] {KEY_GAMES_ID, KEY_GAMES_DATE, KEY_GAMES_PIC, KEY_GAMES_COMPLETE};
		return mDb.query(TABLE_GAMES_NAME, columns, null, null, null, null, null);
	}
	
	public void save(Bundle dump){
		ContentValues values = new ContentValues();
		//java.util.Calendar cal = java.util.Calendar.getInstance();
		SimpleDateFormat formatter = new SimpleDateFormat("MMM d, ''yy H:m");
		Date now = new Date();
		String date = formatter.format(now);
		//String date = cal.get(Calendar.MONTH) + " " + cal.get(Calendar.DAY_OF_MONTH) + ", " +
		//		cal.get(Calendar.YEAR) + " " + cal.get(Calendar.HOUR_OF_DAY) + ":" + cal.get(Calendar.MINUTE);
		long gameId = dump.getLong(KEY_GAMES_ID, -1);
		if(gameId == -1){ //save new game
			//saving game
			values.put(KEY_GAMES_COMPLETE, dump.getInt(KEY_GAMES_COMPLETE));
			values.put(KEY_GAMES_DATE, date);
			values.put(KEY_GAMES_PIC, "pic");
			gameId = mDb.insert(TABLE_GAMES_NAME, null, values);
			Log.i("-= Saver =-", "New game saved, id = " + gameId);
			
			//saving pieces
			ArrayList<ContentValues> field = dump.getParcelableArrayList(GameActivity.BUNDLE_KEY_FIELD);
			for (ContentValues piece : field) {
				piece.put(KEY_PIECES_GAME_ID, (int)gameId);
				mDb.insert(TABLE_PIECES_NAME, null, piece);
			}
			
		} else { //update saved game
			//updating game
			
			values.clear();
			values.put(KEY_GAMES_COMPLETE, dump.getInt(KEY_GAMES_COMPLETE));
			values.put(KEY_GAMES_DATE, date);
			values.put(KEY_GAMES_PIC, "new_pic");
			mDb.update(Saver.TABLE_GAMES_NAME, values, Saver.KEY_GAMES_ID + " = " + gameId , null);
			
			//updating pieces
			String key = null;
			String whereClause = null;
			ArrayList<ContentValues> field = dump.getParcelableArrayList(GameActivity.BUNDLE_KEY_FIELD);
			for (ContentValues piece : field) {
				key = piece.getAsString(Saver.KEY_PIECES_KEY);
				whereClause = "(" + Saver.KEY_PIECES_GAME_ID + " = " + gameId + ")" + " AND " + "(" + Saver.KEY_PIECES_KEY + " = \"" + key + "\")";
				piece.put(KEY_PIECES_GAME_ID, (int)gameId);
				//Log.i("-= Saver =-", "Piece is: " + piece.toString());
				mDb.update(TABLE_PIECES_NAME, piece, whereClause, null);
			}
			Log.i("-= Saver =-", "Game " + gameId + " updated.");
		}
	}
	
	
	public Bundle load(long id){
		/*
		Bundle game = new Bundle();
		//load game
		Cursor gameCursor = mDb.query(TABLE_GAMES_NAME, new String[] {KEY_GAMES_COMPLETE, KEY_GAMES_DATE, KEY_GAMES_PIC},
				KEY_GAMES_ID + " = " + id, null, null, null, null);
		if(gameCursor == null) return null;
		
		gameCursor.moveToFirst();
		game.putInt(KEY_GAMES_COMPLETE, gameCursor.getInt(gameCursor.getColumnIndex(KEY_GAMES_COMPLETE)));
		game.putInt(KEY_GAMES_DATE, gameCursor.getInt(gameCursor.getColumnIndex(KEY_GAMES_DATE)));
		game.putInt(KEY_GAMES_PIC, gameCursor.getInt(gameCursor.getColumnIndex(KEY_GAMES_PIC)));
		Log.i("-= Saver =-", "Game " + id + " loaded.");
		gameCursor.close();
		*/
		//load pieces
		Cursor gameCursor = mDb.query(TABLE_PIECES_NAME, new String[] {KEY_PIECES_KEY, KEY_PIECES_COORD},
				KEY_PIECES_GAME_ID + " = " + id, null, null, null, null);
		if(gameCursor == null) return null;
		Bundle field = new Bundle();
		
		while (gameCursor.moveToNext()) {
			field.putInt(gameCursor.getString(gameCursor.getColumnIndex(KEY_PIECES_KEY)),
					gameCursor.getInt(gameCursor.getColumnIndex(KEY_PIECES_COORD)));
		}
		Log.i("-= Saver =-", "Game`s " + id + " pieces loaded.");
		//game.putParcelableArrayList(GameActivity.BUNDLE_KEY_FIELD, field);
		gameCursor.close();
		return field;
	}
	
	public void close(){
		mDbHelper.close();
	}
	
}
